|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Enhancements
You may want to make some additional enhancements to improve the performance of your data warehouse. Listed here are some of the items that can offer performance improvement; also listed are a few items that do not improve performance. Each item is accompanied by an explanation.
- Block size. In a data warehousing system, you may find that increasing the database block size can greatly improve performance. Because many of the queries access the database in a sequential manner, even though concurrent queries randomize the I/O, having a larger block size brings more of the rows into the SGA at once. Having these additional rows in the SGA can benefit you because you will be using them.
- Clusters. Depending on the application, you may benefit from clusters. The benefit you receive depends completely on the data access patterns and your application. In general, I dont think that clusters help data warehousing systems, except for very specific applications.
- Hash clusters. Because the data warehouse typically uses table scans to get large amounts of data, a hash cluster will most likely degrade performance. Hash clusters are useful when the queries involve equality statements on the hash key.
- Indexes. Again, because table scans are involved in most queries, indexes may not be particularly useful (although some queries may benefit from the use of indexes). How and when you use indexes depends on your application. If you do use indexes, you may want to take advantage of the FULL hint, which bypasses the index, if you know you will be doing a table scan.
- Direct-write sorts. Direct-write sorts is a new feature that allows you to bypass the SGA when performing sort operations. This feature prevents sorts from taking unnecessary space in the SGA; the result is more SGA space for other operations. If you do a lot of sorting, direct-write sorts may be helpful.
- Parallel Query option. The Oracle Parallel Query option can almost certainly improve the performance of your data warehouse. Parallel queries can be performed on sorts, joins, and table scans to improve the performance of those operations.
Many of these enhancements can help your performance; some may degrade performance. The specific effect on your system will vary, but the information given here is true for most general cases.
Parallel Query Option
Probably the best application to take advantage of the Parallel Query option is a DSS systemand the data warehouse is essentially a DSS system. The design of the Parallel Query option is centered around large queries that access large amounts of data (which is a typical feature of a data warehouse query). Here is a short list of some of the operations that can be parallelized:
- Sorts
- Joins
- Table scans
All these query types are typically used in data warehouse queries. Because these queries are typically of long duration and involve large amounts of disk I/O, you can drastically cut the time it takes to complete these queries by using the Parallel Query option.
Once you set up a test database and develop some test queries (hopefully similar to, if not exactly the same as, production queries), run these queries with various degrees of parallelism to determine optimal performance.
If you will run these queries with other queries in production, you should also test with multiple queries. A tuning parameter set up for use with a single query may not be optimal when used with many queries in parallel.
The Parallel Query option can take advantage of the concurrent nature of disk arrays. By splitting up the queries into many small pieces, you may be able to request data from all the drives in your system simultaneously, optimizing I/O performance.
Oracle Parallel Server
The Oracle Parallel Server option can be beneficial to your operation in two ways:
- 1. Performance. If your system is a candidate for the Parallel Server option, you should see significant performance improvements.
- 2. Fault tolerance. By using the Oracle Parallel Server option, you can keep the system running, even if a computer fails. The data warehouse is required to be available for large periods of time, it typically does not demand the same uptime requirements as OLTP systems.
The Oracle Parallel Server option can enhance the performance of your data warehouse, but only if your system is suited for parallel servers. To see scaleable performance increases, your system must meet the following criteria:
- Partitionable data. The data is partitionable. In other words, the work related to certain tables can be done on one computer while work related to other tables is done on another computer. If all users access the exact same data, your application may not be a good candidate for the Parallel Server option. Although some overlap is fine, the majority of the data should be partitionable.
- Many processes. If you have only a few users and concurrent transactions, you probably are not a good candidate for the Parallel Server option. However, if you use the Parallel Query option, you may use a sufficient number of query server processes to satisfy this requirement.
If these conditions are met, you will probably benefit from using the Oracle Parallel Server option.
NOTE: The Oracle Parallel Server option is not available on all platforms. Consult your system provider or Oracle to determine which platforms support the Oracle Parallel Server option.
|